var express = require('express');
var router = express.Router();
var utils = require('../../functions/utils');

router.get('/' , function(req,res)
{
	var html = "<form name='tmpForm' method='post' action='/editStatistics'></form>";
	html +=	"<script language=javascript>tmpForm.submit();</script>";

	res.writeHead(200 , {'Content-Type' : 'text/html; charset=utf-8'});
	res.end(html);

});

function makeSelectOsTypeString(type)
{
		var str  = '';

		str += "<option  value='all' ";
		if(type == 'all')
			str += "SELECTED ";
		str += ">ALL</option>";

		str += "<option  value='android' ";
		if(type == 'android')
			str += "SELECTED ";
		str += ">Android</option>";

		str += "<option  value='ios' ";
		if(type == 'ios')
			str += "SELECTED ";
		str += ">iOS</option>";

		return str;
}


function makeSelectOrderTypeString(type)
{
		var str  = '';

		str += "<option  value='dateDesc' ";
		if(type == 'dateDesc')
			str += "SELECTED ";
		str += ">▼ DATE</option>";

		str += "<option  value='dateAsc' ";
		if(type == 'dateAsc')
			str += "SELECTED ";
		str += ">▲ DATE</option>";
		////////////////////////////////////
		str += "<option  value='startCountDesc' ";
		if(type == 'startCountDesc')
			str += "SELECTED ";
		str += ">▼ START COUNT</option>";

		str += "<option  value='startCountAsc' ";
		if(type == 'startCountAsc')
			str += "SELECTED ";
		str += ">▲ START COUNT</option>";
		////////////////////////////////////
		str += "<option  value='registerCountDesc' ";
		if(type == 'registerCountDesc')
			str += "SELECTED ";
		str += ">▼ REGISTERED COUNT</option>";

		str += "<option  value='registerCountAsc' ";
		if(type == 'registerCountAsc')
			str += "SELECTED ";
		str += ">▲ REGISTERED COUNT</option>";
		////////////////////////////////////
		str += "<option  value='cancelCountDesc' ";
		if(type == 'cancelCountDesc')
			str += "SELECTED ";
		str += ">▼ CANCELED COUNT</option>";

		str += "<option  value='cancelCountAsc' ";
		if(type == 'cancelCountAsc')
			str += "SELECTED ";
		str += ">▲ CANCELED COUNT</option>";
		////////////////////////////////////
		str += "<option  value='notRegisterCountDesc' ";
		if(type == 'notRegisterCountDesc')
			str += "SELECTED ";
		str += ">▼ NOT REGISTERED COUNT</option>";

		str += "<option  value='notRregisterCountAsc' ";
		if(type == 'notRregisterCountAsc')
			str += "SELECTED ";
		str += ">▲ NOT REGISTERED COUNT</option>";
		////////////////////////////////////

		return str;
}


function makeSelectYearString(year)
{
		var str  = '';
		for(var i = 2014 ; i <= (new Date()).getFullYear() ; i ++)
		{
			if(year == i)
				str += "<option value='" + i + "' SELECTED>"+i+"</option>";
			else
				str += "<option value='" + i + "'>"+i+"</option>";
		}

		return str;
};

function makeSelectMonthString(mon)
{
		var str  = '';
		for(var i = 1 ; i <= 12 ; i ++)
		{
			if(mon == i)
				str += "<option value='" + utils.attachZero(i,2) + "' SELECTED>" + utils.attachZero(i,2) + "</option>";
			else
				str += "<option value='" + utils.attachZero(i,2) + "'>" + utils.attachZero(i,2) + "</option>";
		}

		return str;
};

function makeSelectDayString(day)
{
		var str  = '';
		for(var i = 1 ; i <= 31 ; i ++)
		{
			if(day == i)
				str += "<option value='" + utils.attachZero(i,2) + "' SELECTED>" + utils.attachZero(i,2) + "</option>";
			else
				str += "<option value='" + utils.attachZero(i,2) + "'>" + utils.attachZero(i,2) + "</option>";
		}

		return str;
};


function saveFile(filename , str)
{
	//save csv file
	require('fs').writeFile(global.config.storage.statistics.localDirectory + filename, str, function(err) {
			if(err) throw err;
			console.log('csv File write completed');
		});
}

function makeDataSheet(data , csvFilename)
{
	var str = '<tr bgcolor=#9BDCF2><td rowspan=2>&nbsp;<b>NO&nbsp;</td><td rowspan=2>&nbsp;<b>DATE&nbsp;</td><td rowspan=2>&nbsp;<b>STARTED&nbsp;</td><td colspan=2>&nbsp;<b>REGISTERED&nbsp;</td><td colspan=2>&nbsp;<b>CANCELED&nbsp;</td><td colspan=2>&nbsp;<b>ETC. FAILED&nbsp;</td><td colspan=2>&nbsp;<b>NOT REGISTERED&nbsp;</td></tr>';
	str += '<tr bgcolor=#9BDCF2><td>&nbsp;<b>count</td><td>&nbsp;<b>ratio(%)</td><td>&nbsp;<b>count</td><td>&nbsp;<b>ratio(%)</td><td>&nbsp;<b>count</td><td>&nbsp;<b>ratio(%)</td><td>&nbsp;<b>count</td><td>&nbsp;<b>ratio(%)</td></tr>';
	var csv = "\"NO\",\"DATE\",\"STARTED COUNT\",\"REGISTERED COUNT\",\"REGISTERED RATIO(%)\",\"CANCELED COUNT\",\"CANCELED RATIO(%)\",\"ETC. FAILED COUNT\",\"ETC. FAILED RATIO(%)\",\"NOT REGISTERED COUNT\",\"NOT REGISTERED RATIO(%)\"";

	var totalStarted = 0;
	var totalRegistered = 0;
	var totalCanceled = 0;
	var totalEtcFailed = 0;
	var totalNotRegistered = 0;

	var rr , cr , er , nr;

	for(var i = 0 ; i < data.length ; i  ++)
	{
		rr = ( data[i].registeredCount / data[i].startedCount *100 ).toFixed(2);
		cr = ( data[i].canceledCount / data[i].startedCount *100 ).toFixed(2);
		er = ( data[i].etcFailedCount / data[i].startedCount *100 ).toFixed(2);
		nr = ( data[i].notRegisteredCount / data[i].startedCount * 100).toFixed(2);

		str += '<tr><td>&nbsp;'+(i+1)+'&nbsp;</td><td>&nbsp;'+data[i].date+'&nbsp;</td><td>&nbsp;'+data[i].startedCount+'&nbsp;</td>';
		str += '<td>&nbsp;'+data[i].registeredCount+'&nbsp;</td><td>&nbsp;<font color=blue>'+rr+'&nbsp;</td>';
		str += '<td>&nbsp;'+data[i].canceledCount+'&nbsp;</td><td>&nbsp;<font color=red>'+cr+'&nbsp;</td>';
		str += '<td>&nbsp;'+data[i].etcFailedCount+'&nbsp;</td><td>&nbsp;<font color=red>'+er+'&nbsp;</td>';
		str += '<td>&nbsp;'+data[i].notRegisteredCount+'&nbsp;</td><td>&nbsp;<font color=red>'+nr+'&nbsp;</td></tr>';		


		csv += "\r\n\""+(i+1)+"\","+data[i].date;
		csv += ","+data[i].startedCount;
		csv += ","+data[i].registeredCount+","+rr;
		csv += ","+data[i].canceledCount+","+cr;
		csv += ","+data[i].etcFailedCount+","+er;
		csv += ","+data[i].notRegisteredCount + ","+nr;


		totalStarted += data[i].startedCount;
		totalRegistered += data[i].registeredCount;
		totalCanceled += data[i].canceledCount;
		totalEtcFailed += data[i].etcFailedCount;
		totalNotRegistered += data[i].notRegisteredCount;

	}

	rr = ( totalRegistered / totalStarted *100 ).toFixed(2);
	cr = ( totalCanceled / totalStarted *100 ).toFixed(2);
	er = ( totalEtcFailed / totalStarted *100 ).toFixed(2);
	nr = ( totalNotRegistered / totalStarted *100 ).toFixed(2);


	str += '<tr bgcolor=#CCD9DE><Td colspan=2><b><center>TOTAL</center></td><td>&nbsp;<b>'+totalStarted+'</td>';
	str += '<td>&nbsp;<b>'+totalRegistered+'</td><td>&nbsp;<font color=blue><b>'+rr+'%</td>';
	str += '<td>&nbsp;<b>'+totalCanceled+'</td><td>&nbsp;<font color=red><b>'+cr+'%</td>';
	str += '<td>&nbsp;<b>'+totalEtcFailed+'</td><td>&nbsp;<font color=red><b>'+er+'%</td>';
	str += '<td>&nbsp;<b>'+totalNotRegistered+'</td><td>&nbsp;<font color=red><b>'+nr+'%</td></tr>';

	saveFile(csvFilename , csv);

	return str;
}



router.post('/', function(req,res)
{
	var sessionKey = req.session.key;

	if(utils.isValidString(sessionKey))
	{
		var async = require('async');
		async.waterfall([
			function(cb)
			{
				utils.checkManagerSession(sessionKey , cb);
			}
			,function(result , cb) 
			{
				// 실패 - 로그인 페이지 
				if(result == 0)
				{
					utils.notifyManagerSessionExpired(req , res);
				}
				// 성공 - 실제 작업코드.
				else
				{	
				/////////////////////////////////////////////////////////////////////////////////////////////	
				/*
					Input parameters 

					osType 
					orderType

					startYear
					startMonth
					startDay
												   
					endYear
					endMonth
					endDay

				*/
					var osType = req.body.osType;		
					if(!utils.isValidString(osType))
						osType = 'all';

					var orderType = req.body.orderType;		
					if(!utils.isValidString(orderType))
						orderType = 'dateDesc';


					var startYear = req.body.startYear;		
					var startMonth = req.body.startMonth;		
					var startDay = req.body.startDay;		

					var endYear = req.body.endYear;		
					var endMonth = req.body.endMonth;		
					var endDay = req.body.endDay;		

					// trace log 중 edit 관련항목 사용.
					var db = global.log_db;

					// 안넘어오면 오늘날짜 첨부터 끝까지 
					if(!utils.isValidString(startYear))
					{
						var today = new Date();
						var ago = new Date();
						ago.setDate(today.getDate() - 5); // 5일 동안.

						startYear = String(ago.getFullYear());
						startMonth = utils.attachZero(ago.getMonth()+1 , 2); //January is 0!
						startDay = utils.attachZero(ago.getDate() , 2);

						endYear = String(today.getFullYear());
						endMonth = utils.attachZero(today.getMonth()+1 , 2); //January is 0!
						endDay = utils.attachZero(today.getDate() , 2);
					}

					var startDate = startYear+'-'+startMonth+'-'+startDay+' 00:00:00';
					var endDate = endYear+'-'+endMonth+'-'+endDay+' 23:59:59';


					var fs = require('fs');
					fs.readFile('./template/editStatistics' , function(error,data)
					{
						if(error)
						{
							throw error;
						}
						var html = String(data);


						db.connection.getConnection(function(err,connection)
						{
							if(err)
							{
								throw err;
							}

							
							var input_param = [startDate, endDate , osType , orderType];

							var sql = 'call manage_getEditStatistics(?, ?, ? , ? )';

							var query = connection.query(sql, input_param , function (err, rows) 
							{
								connection.release();
								if(err)
								{
									throw err;
								}


								var strSelect = makeSelectOsTypeString(osType);
								html = html.split("%OS_TYPE%").join(strSelect); 

								strSelect = makeSelectOrderTypeString(orderType);
								html = html.split("%ORDER_TYPE%").join(strSelect); 


								strSelect = makeSelectYearString(Number(startYear));
								html = html.split("%START_YEAR%").join(strSelect); 
								strSelect = makeSelectMonthString(Number(startMonth));
								html = html.split("%START_MONTH%").join(strSelect); 
								strSelect = makeSelectDayString(Number(startDay));
								html = html.split("%START_DAY%").join(strSelect); 


								strSelect = makeSelectYearString(Number(endYear));
								html = html.split("%END_YEAR%").join(strSelect); 
								strSelect = makeSelectMonthString(Number(endMonth));
								html = html.split("%END_MONTH%").join(strSelect); 
								strSelect = makeSelectDayString(Number(endDay));
								html = html.split("%END_DAY%").join(strSelect); 
							

								var dd = new Date();
								var csvFilename = 'user_'	+ utils.attachZero(dd.getUTCFullYear() , 4) + utils.attachZero(dd.getUTCMonth()+1 , 2) + utils.attachZero(dd.getUTCDate(),2) 
										+ utils.attachZero(dd.getUTCHours(),2) + utils.attachZero(dd.getUTCMinutes(),2)+ utils.attachZero(dd.getUTCSeconds(),2) + '.csv'; 

								// 화면만들고 csv파일만든다.				
								html = html.split("%DATA_SHEET%").join(makeDataSheet(rows[0],csvFilename));

								// html안의 경로
								html = html.split("%EXCEL_FILENAME%").join(csvFilename);

								res.writeHead(200 , {'Content-Type' : 'text/html; charset=utf-8'});
								res.end(html);


							}); // query

						}); // getConnection

					}); // readfile
			//////////////////////////////////////////////////////////////////////////////////////////

				} // result==1 
			}
		]); //waterfall
	}
	// 암것도 없으면 loginPage
	else
	{
		utils.gotoManagerLoginPage(req , res);
	}
});

module.exports = router;